﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using Model;

namespace MsDAL
{
    public partial class Sys0a02DAL : Sys0a02Base
    {
        public DataTable GetListByRoleCode(string rolcode)
        {
            string sql = "SELECT B.usercode,B.username,'Y' as flag "
                 + " FROM Sys0a02 A INNER JOIN Sys0a00 B ON A.usercode = B.usercode "
                 + " WHERE A.rolecode =@rolecode ";

            return DBHelper.GetData(sql, new SqlParameter("@rolecode", rolcode));
        }

        public SqlApp DeleteByRolecode(string rolecode)
        {
            string sql = "delete from Sys0a02 WHERE rolecode = @rolecode";

            SqlParameter[] parameters = {
				new SqlParameter("@rolecode",SqlDbType.NVarChar)
				};
            parameters[0].Value = rolecode;

            return new SqlApp(sql, new ArrayList(parameters), true, "");
        }

        /// <summary>
        /// 在不存在的情况下新增数据
        /// </summary>
        public SqlApp AddIfNotExists(Model.Sys0a02 sys0a02)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Sys0a02(");
            strSql.Append("usercode ,rolecode )");
            strSql.Append(" values (");
            strSql.Append("@usercode ,@rolecode )");

            string sql = "insert into Sys0a02(usercode ,rolecode )"
                + " SELECT @usercode ,@rolecode WHERE "
                + " NOT EXISTS(SELECT 1 FROM Sys0a02 WHERE usercode=@usercode AND rolecode =@rolecode) ";

            SqlParameter[] parameters = {
				new SqlParameter("@usercode",SqlDbType.NVarChar),
				new SqlParameter("@rolecode",SqlDbType.NVarChar)
				};
            parameters[0].Value = sys0a02.Usercode;
            parameters[1].Value = sys0a02.Rolecode;

            return new SqlApp(sql, new ArrayList(parameters), true, "");
        }

        public SqlApp DeleteByUsercode(string usercode)
        {
            string sql = "DELETE FROM Sys0a02 WHERE usercode =@usercode";

            SqlParameter[] parameters = {
				new SqlParameter("@usercode",SqlDbType.NVarChar)
				};
            parameters[0].Value = usercode;

            return new SqlApp(sql, new ArrayList(parameters), true, "");
        }
    }
}
